package cn.com.dashihui.web.controller;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.shiro.authz.annotation.RequiresAuthentication;

import com.jfinal.kit.PropKit;
import com.jfinal.kit.StrKit;
import com.jfinal.plugin.activerecord.Record;
import com.jfinal.upload.UploadFile;

import cn.com.dashihui.kit.CEVUtil;
import cn.com.dashihui.kit.CommonKit;
import cn.com.dashihui.kit.DatetimeKit;
import cn.com.dashihui.kit.ExcelExportUtil.Pair;
import cn.com.dashihui.web.base.BaseController;
import cn.com.dashihui.web.dao.AuthDownload;
import cn.com.dashihui.web.dao.Store;
import cn.com.dashihui.web.dao.User;
import cn.com.dashihui.web.dao.UserChange;
import cn.com.dashihui.web.dao.UserChangeExcel;
import cn.com.dashihui.web.service.AuthDownloadService;
import cn.com.dashihui.web.service.ChangeSyncService;

@RequiresAuthentication
public class ChangeSyncController extends BaseController{
	private ChangeSyncService service = new ChangeSyncService();
	private AuthDownloadService downloadService=new AuthDownloadService();

    
    public void index(){
        render("index.jsp");
    }
    /**
     * 查询页面数据，并返回
     */
    public void page(){
    	int pageNum = getParaToInt(0, 1);
		String phone=getPara("phone");
		String card=getPara("card");
		int match=getParaToInt("match",0);
		int pageSize = getParaToInt("pageSize", PropKit.getInt("constants.pageSize"));
		renderResult(0,service.findByPage(pageNum, pageSize,phone,card,match));
	}
    /**
     * 查询导入日志，并返回
     */
    public void importLog(){
    	int pageNum = getParaToInt(0, 1);
		int pageSize = getParaToInt("pageSize", PropKit.getInt("constants.pageSize"));
		renderResult(0,service.importLog(pageNum, pageSize));
	}
    /**
	 * 会员详情
	 */
	public void detail(){
		int id = getParaToInt(0,0);
		if(id!=0){
		User user=service.findById(id);
			setAttr("user", user);
			List<Store> storeList=service.findStoreList();
			setAttr("storeList", storeList);
		}
		render("detail.jsp");
	}
	/**
	 * excel导出
	 */
	public void doExcel(){
		List<Pair> titles = new ArrayList<Pair>();
		String phone=getPara("phone");
		String card=getPara("card");
		int match = getParaToInt("match",0);
	    titles.add(new Pair("phone", "客户名称"));
	    titles.add(new Pair("clientCode", "客户编码"));
	    titles.add(new Pair("userType", "客户类型"));
	    titles.add(new Pair("name", "法人代表"));
	    titles.add(new Pair("bankNoId", "开户银行"));
	    titles.add(new Pair("cardNo", "银行账号"));
	    titles.add(new Pair("userType", "证件类型"));
	    titles.add(new Pair("cardIdNo", "证件号"));  
	    titles.add(new Pair("phone", "手机"));
	    titles.add(new Pair("email", "邮箱"));
	    titles.add(new Pair("address", "详细地址"));
	    titles.add(new Pair("toBossOneId", "推荐人"));
	    titles.add(new Pair("createDate", "担保商"));
		List<Record> list=service.findExcelByPage(phone,card,match);
		List<Record> listz=new ArrayList<Record>();
		for (int i = 0; i < list.size(); i++) {
			Record record=new Record();
				
				record.set("phone", list.get(i).get("phone"));
				record.set("clientCode",list.get(i).get("clientCode"));
				record.set("userType",list.get(i).get("userType"));
				record.set("name", list.get(i).get("name"));
				record.set("bankNoId", list.get(i).get("bankNoId"));
				record.set("cardIdNo", list.get(i).get("cardIdNo"));
				record.set("userType",list.get(i).get("userType"));
				record.set("cardNo", list.get(i).get("bankCardNo"));
				record.set("phone", list.get(i).get("phone"));
				record.set("email", list.get(i).get("email"));
				record.set("address", list.get(i).get("address"));
			listz.add(record);
 		}

		//向登录下载统计表中添加数据 会员积分统计
		Record downObj=downloadService.findByUserId(getCurrentUser().getInt("id"));
		AuthDownload download=new AuthDownload();
		download.set("logName","平台端")
			        .set("name", downObj.get("trueName"))
			        .set("accountRole", downObj.get("alias"))
			        .set("storeName", "公司总部")
			        .set("integralDownload", 1)
                   .set("downloadDate",DatetimeKit.getFormatDate("yyyy-MM-dd HH:mm:ss"));

		download.save();
		// 导出
		service.export(getResponse(), getRequest(),titles, listz);		
		renderNull();
	}
	
	/**
	 * 跳转到批量导入页面
	 */
	public void toImportExcel() {
		List<UserChangeExcel> userChangeExcels = service.findUserChangeExcel();
		setAttr("list", userChangeExcels);
		render("import.jsp");
	}
	/**
	 * 下载excel模板
	 */
	public void downExcelTemplate() {
//		String dir = DirKit.getDir(DirKit.UPLOAD);
		String filepath = getRequest().getServletContext().getRealPath("/")+"/upload/excelTemplate.xlsx";
		File file = new File(filepath);
		renderFile(file);
	}
	
	/**
	 * 导入Excel
	 * @throws IOException 
	 * @throws FileNotFoundException 
	 */
	public void doImportExcel(){
		UploadFile uploadFile = getFile();
		if (uploadFile == null) {
			renderFailed("文件不能为空");
			return;
		}
		File file = uploadFile.getFile();
		String fileName = file.getName();
		
		Record result = dealExcel(file);
		//处理文件问题
		if (result.getBoolean("flag")) {
			List<UserChange> list = result.get("data");
			List<UserChange> successList = new ArrayList<>();
			List<UserChange> errorList = new ArrayList<>();
			//保存list数据
			if (!list.isEmpty()) {
				for(int i=0; i<list.size(); i++) {
					UserChange userChange = list.get(i);
					String clientName = userChange.getStr("clientName");
					//校验手机号、身份证号在系统中是否存在
					if (ChangeSyncService.checkTelephoneIsExist(clientName)) {
						
						errorList.add(userChange);
						continue;
					}
					int match = 1 ;
					userChange.set("match", match)
						.set("createDate", new Date())
						.set("refreshDate", new Date());
					if (userChange.save()) {
						successList.add(userChange);
					}
				}
			}
			String  cause = "成功添加" + successList.size() + "个会员账号";
			if ((list.size()-successList.size()-errorList.size()) != 0) {
				cause += "," +"其中新添加" + (list.size()-successList.size()-errorList.size()) + "个用户";
			}
			if (!errorList.isEmpty()) {
				cause += "," + errorList.size() + "个已存在";
			}
			UserChangeExcel unionMemberExcel = new UserChangeExcel()
					.set("memberId", getCurrentUser().get("id"))
					.set("fileName", fileName)
					.set("totalNum", list.size())
					.set("successNum", successList.size())
					.set("failNum", errorList.size())
					.set("status", 1)
					.set("cause", cause)
					.set("createTime", new Date());
			unionMemberExcel.save();
			renderSuccess();
		}else {
			renderFailed((String) result.get("error"));
		}
	}
	/**
	 * 处理Excel
	 * @param file
	 * @return
	 */
	private Record dealExcel(File file){
		Record record = new Record();
		record.set("flag", false);
		record.set("error", "");
		String fileName = file.getName();
		if (CEVUtil.isExcel(fileName)) {
			List<UserChange> list = new ArrayList<>();
			Workbook workbook = null;
			Sheet sheet = null;
			Row row = null;
			//验证Excel内容
			try {
				if (CEVUtil.isExcel2003(fileName)) {
						InputStream fileInputStream = new FileInputStream(file);
						workbook =  new HSSFWorkbook(fileInputStream);
				}else if (CEVUtil.isExcel2007(fileName)){
						InputStream fileInputStream = new FileInputStream(file);
						workbook = new XSSFWorkbook(fileInputStream);
				}else {
					record.set("error", "请选择excel类型文件");
				}
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
				sheet = workbook.getSheetAt(0);
				//因为获取最后一行的航标不excel的行标小1，所以此处加1
				int rows = (sheet.getLastRowNum())+1;
				System.out.println(rows);
				if (rows == 1) {
					record.set("error", "Excel内容为空");
				}else {
					//判断表头是否正确
					for(int i=1; i<rows; i++) {
						row = sheet.getRow(i);// 行数
						if (row!=null) {
							row.getCell(0).setCellType(Cell.CELL_TYPE_STRING);
							String clientCode = row.getCell(0).getStringCellValue();// 客户代码
							row.getCell(1).setCellType(Cell.CELL_TYPE_STRING);
							String clientName = row.getCell(1).getStringCellValue();// 客户名称
							row.getCell(2).setCellType(Cell.CELL_TYPE_STRING);
							String clientType = row.getCell(2).getStringCellValue();// 客户类型
							row.getCell(3).setCellType(Cell.CELL_TYPE_STRING);
							String cardType = row.getCell(3).getStringCellValue();// 证件类型
							row.getCell(4).setCellType(Cell.CELL_TYPE_STRING);
							String cardNum = row.getCell(4).getStringCellValue();// 证件编码
							row.getCell(5).setCellType(Cell.CELL_TYPE_STRING);
							String bankName = row.getCell(5).getStringCellValue();// 开户银行
							row.getCell(6).setCellType(Cell.CELL_TYPE_STRING);
							String bankNum = row.getCell(6).getStringCellValue();// 银行账号
							row.getCell(7).setCellType(Cell.CELL_TYPE_STRING);
							String state = row.getCell(7).getStringCellValue();// 签约状态
							row.getCell(8).setCellType(Cell.CELL_TYPE_STRING);
							String referrerCode = row.getCell(8).getStringCellValue();// 推荐客户编码
							row.getCell(9).setCellType(Cell.CELL_TYPE_STRING);
							String referrerName = row.getCell(9).getStringCellValue();// 推荐客户姓名
							row.getCell(10).setCellType(Cell.CELL_TYPE_STRING);
							String twoReferrerCode = row.getCell(10).getStringCellValue();// 间接推荐客户编码
							row.getCell(11).setCellType(Cell.CELL_TYPE_STRING);
							String twoReferrerName = row.getCell(11).getStringCellValue();// 间接推荐客户姓名
							row.getCell(12).setCellType(Cell.CELL_TYPE_STRING);
							String organ = row.getCell(12).getStringCellValue();// 所属区域
							row.getCell(13).setCellType(Cell.CELL_TYPE_STRING);
							String superOrgan = row.getCell(13).getStringCellValue();// 直属上级
							row.getCell(14).setCellType(Cell.CELL_TYPE_STRING);
							String salesMan = row.getCell(14).getStringCellValue();// 代理商
							row.getCell(15).setCellType(Cell.CELL_TYPE_STRING);
							String linkMan = row.getCell(15).getStringCellValue();// 联系人
							row.getCell(16).setCellType(Cell.CELL_TYPE_STRING);
							String linkPhone = row.getCell(16).getStringCellValue();// 联系人电话

							if (!StrKit.notNull(clientCode, clientName, clientType, cardType, cardNum, bankName,
									bankNum, state, referrerCode,referrerName,twoReferrerCode,twoReferrerName,organ,
									superOrgan,linkMan,linkPhone)) {
								record.set("error", "导入文件不符合标准，请重新上传文件");
								break;
							}
							//获取用户信息，验证数据库是否存在当前用户
							User user = service.findByUserPhone(linkPhone);
							if (user == null) {
								String cards=DatetimeKit.getFormatDate("yyyyMMddHHmmssSSS");
								String card=cards.substring(2);
								int newstate = 3;
								int newtaici = 1;
								String address = "请修改您的地址";
								//银行的id
								int bankId = 0;
								Record bank =null;
								if (bankName!=null) {
									bank = service.findBankIdByBankName(bankName);
								}
								if (bank!=null) {
									bankId = bank.get("bankId");
								}

								String password = linkPhone.substring(linkPhone.length() - 6);
								User byUserPhone = service.findByUserPhone(linkPhone);
								if (byUserPhone == null) {
									User newUser = new User();
									newUser.set("name", clientName)
										.set("password",CommonKit.encryptPassword(password))
										.set("phone", linkPhone)
										.set("card", card)
										.set("state", newstate)
										.set("taici", newtaici)
										.set("createDate", new Date())
										.set("address", address)
										.set("cardIdNo", cardNum)
										.set("cardNo", bankNum)
										.set("bankNoId", bankId)
										.save();
								}
							}
							System.out.println("转换为UserChange对象");
							UserChange userChange = new UserChange();
							userChange.set("clientCode", clientCode);
							userChange.set("clientName", clientName);
							userChange.set("clientType", clientType);
							userChange.set("cardType", cardType);
							userChange.set("cardNum", cardNum);
							userChange.set("bankName", bankName);
							userChange.set("bankNum", bankNum);
							userChange.set("state", state);
							userChange.set("referrerCode", referrerCode);
							userChange.set("referrerName", referrerName);
							userChange.set("twoReferrerCode", twoReferrerCode);
							userChange.set("twoReferrerName", twoReferrerName);
							userChange.set("organ", organ);
							userChange.set("superOrgan", superOrgan);
							userChange.set("salesMan", salesMan);
							userChange.set("linkMan", linkMan);
							userChange.set("linkPhone", linkPhone);

							list.add(userChange);
						}
					}
				}
				logger.info("Excel中的数据一共有【" + list.size() + "】条。");
				record.set("flag", true);
				record.set("data", list);
		}else {
			record.set("error", "请选择excel类型文件");
		}
		return record;
	}
}
